package tripx.db;

import java.sql.ParameterMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSetMetaData;
import com.mysql.jdbc.Statement;

import tripx.db.system.Logger;
import tripx.db.system.TripxSystem;
import tripx.util.StringHelper;

/**
 * The Service provide to the upper layer to operate on the database system
 * 
 * @author zhb
 * 
 */
public class DbService {

	/**
	 * for database connection
	 */
	private DbConnector connector;
	/**
	 * for common property
	 */
	private DbServiceProperty property;
	/**
	 * for log output
	 */
	private Logger logger;

	public DbService() {
		connector = TripxSystem.getInstance().getDbConnector();
		property = DbServiceProperty.getInstance();
		logger = Logger.getInstance();
	}

	/**
	 * execute the given sql statement. You must provide statement which may be
	 * insert, delete or update
	 * 
	 * @param sql
	 * @return either (1) the row count for SQL Data Manipulation Language (DML)
	 *         statements or (2) 0 for SQL statements that return nothing
	 */
	public int executeUpdateSql(String sql) {
		logger.info("Execute update sql:" + sql);
		Connection conn = connector.getConnection();
		int rowCount = 0;
		try {
			Statement statement = (Statement) conn.createStatement();
			rowCount = statement.executeUpdate(sql);
			statement.close();
		} catch (SQLException e) {
			logger.err(sql + " can not be executed");
		}
		logger.info("End.");
		return rowCount;
	}

	/**
	 * execute the given sql statement, You must provide statement which may
	 * retrieve data with a select form.
	 * 
	 * @param sql
	 * @return
	 */
	public String executeQuerySql(String sql) {
		logger.info("Execute query sql:" + sql);
		Connection conn = connector.getConnection();
		StringBuilder row = new StringBuilder("[");
		try {
			Statement statement = (Statement) conn.createStatement(
					ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			ResultSet resultSet = statement.executeQuery(sql);
			ResultSetMetaData metaData = (ResultSetMetaData) resultSet
					.getMetaData();
			resultSet.beforeFirst();
			while (resultSet.next()) {
				row.append('{');
				for (int i = 0; i < metaData.getColumnCount(); i++) {
					// column index start from 1
					String columnName = metaData.getColumnName(i + 1);
					String value = resultSet.getString(i + 1);
					row.append("\"" + columnName + "\"" + ":\""
							+ StringHelper.strip(value) + "\", ");
				}
				row.delete(row.length() - 2, row.length());
				row.append("}, ");
			}
		} catch (SQLException e) {
			logger.err("Can not execute " + sql);
			return null;
		}
		if (row.length() >= 2) {
			row.delete(row.length() - 2, row.length());
		}
		row.append(']');
		logger.info("\tSending: " + row.toString());
		logger.info("End.");
		return row.toString();
	}

	/**
	 * You can create a <code><b>PreparedStatement</b></code> with this
	 * interface
	 * 
	 * @param sql
	 * @param key
	 */
	public int addPrepareSql(String sql, String key) {
		logger.info("Add prepare sql: key=" + key + ", sql=" + sql);
		// check whether the key is already exist
		if (property.containsKey(key)) {
			logger.info("\t" + key + " is already exist");
			return 0;
		}
		Connection conn = connector.getConnection();
		try {
			PreparedStatement ps = (PreparedStatement) conn
					.prepareStatement(sql);
			property.addPreparedStatement(key, ps);
			logger.info("\tAdd prepare sql successfully\nEnd.");
			return 1;
		} catch (SQLException e) {
			logger.info("\tAdd prepare sql failed.\nEnd.");
			return 0;
		}
	}

	/**
	 * execute the prepare statement statement with the given parameters.
	 * 
	 * @param key
	 *            the index key of the PreparedStatement
	 * @param params
	 *            the parameters
	 * @return
	 */
	public int executeUpdateSql(String key, Object[] params) {
		logger.info("Execute update sql: " + key);
		PreparedStatement ps = property.getPreparedStatement(key);
		if (ps == null) {
			logger.err("\tCan not find the prepared statement for " + key);
			return 0;
		}
		try {
			ParameterMetaData pmd = ps.getParameterMetaData();
			if (pmd.getParameterCount() != params.length) {
				return 0;
			}
			for (int i = 0; i < params.length; i++) {
				String param = (String) params[i];
				ps.setString(i + 1, param);
			}
			// execute the sql statement
			String sql = ps.toString();
			logger.info("\texecute: " + sql.substring(sql.indexOf(":") + 2));
			return ps.executeUpdate();
		} catch (SQLException e) {
			logger.err("\tCan not execute " + ps.toString());
		}
		return 0;
	}

	/**
	 * execute the query statement, since it is the PreparedStatment, you should
	 * provide the extra parameter.
	 * 
	 * @param key
	 * @param params
	 * @return
	 */
	public String executeQuerySql(String key, Object[] params) {
		logger.info("Execute query sql: " + key);
		PreparedStatement ps = property.getPreparedStatement(key);
		if (ps == null) {
			logger.err("\tCan not find the prepared statement for " + key);
			return "[]";
		}
		StringBuilder result = new StringBuilder("[");
		try {
			ParameterMetaData pmd = ps.getParameterMetaData();
			if (pmd.getParameterCount() != params.length) {
				logger.err("Parameter number not matched when handle "
						+ ps.toString());
				return "[]";
			}
			for (int i = 0; i < params.length; i++) {
				String param = (String) params[i];
				ps.setString(i + 1, param);
			}
			// execute the sql statement
			ResultSet resultSet = ps.executeQuery();
			ResultSetMetaData metaData = (ResultSetMetaData) resultSet
					.getMetaData();

			resultSet.beforeFirst();
			while (resultSet.next()) {
				result.append('{');
				for (int i = 0; i < metaData.getColumnCount(); i++) {
					// column index start from 1
					String columnName = metaData.getColumnName(i + 1);
					String value = resultSet.getString(i + 1);
					result.append("\"" + columnName + "\"" + ":\""
							+ StringHelper.strip(value) + "\", ");
				}
				result.delete(result.length() - 2, result.length());
				result.append("}, ");
			}
			if (result.length() > 1) {
				result.delete(result.lastIndexOf(","), result.length());
			}
			result.append("]");
		} catch (SQLException e) {
			logger.err("can not execute " + ps.toString());
		}
		return result.toString();
	}

	/**
	 * check whether the key specify a PreparedStatement
	 * 
	 * @param key
	 * @return
	 */
	public boolean prepareKeyExist(String key) {
		return property.containsKey(key);
	}
}
